package com.atguigu5;

import com.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * 批量添加
 * @auther user
 * @date 2021/12/8 - 17:42
 */
public class BtachTest {
    //方式1：Statement
    /**
     * Connection connection = JDBCUtils.getConnection();
     * Statement statement=connection.createStatement();
     * for(int i = 1,i<=20000,i++){
     *     String sql = "insert into goods(name) values('name_"+i+"')";
     *     statement.execute(sql);
     * }
     */
    //方式二：PreparedStatement
    //  使用一套模板，第一条验证后，其他不再进行安全验证
    //  缓存模板，只需填充占位符
    @Test
    public void testInert1() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {

            long start = System.currentTimeMillis();

            //1 获取连接
            connection = JDBCUtils.getConnection();
            //2 预编译sql,返回PreparedStatment实例
            String sql = "insert into goods(name) values(?)";
            preparedStatement = connection.prepareStatement(sql);
            //3 填充占位符
            for (int i = 0; i < 2000; i++) {
                preparedStatement.setObject(1,"name_"+(i+1));
                //4 执行
                preparedStatement.executeUpdate();
                System.out.println("name:"+"name_"+(i+1));
            }

            long stop = System.currentTimeMillis();
            System.out.println("执行时间为："+(stop-start));//11678
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5 关闭资源
            JDBCUtils.closeResource(connection,preparedStatement);
        }
    }
    //方式三：
    /**
     *          1 addBatch(),executeBatch(),clearBatch()
     *          2 mysql默认不支持
     * 修改2：mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
     * 	  		 ?rewriteBatchedStatements=true 写在配置文件的url后面
     * 	  		修改3：使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar
     */
    @Test
    public void testInsert2() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            long start = System.currentTimeMillis();

            //1 获取连接
            connection = JDBCUtils.getConnection();
            //2 预编译sql,返回PreparedStatment实例
            String sql = "insert into goods(name) values(?)";
            preparedStatement = connection.prepareStatement(sql);
            //3 填充占位符
            for (int i = 0; i < 2000; i++) {
                preparedStatement.setObject(1,"name_"+(i+1));
                //4 执行
                //4.1 攒sql
                preparedStatement.addBatch();
                if ((i+1) % 500 == 0){
                    //4.2 执行
                    preparedStatement.executeBatch();
                    //4.3 清空Batch
                    preparedStatement.clearBatch();
                }
            }

            long stop = System.currentTimeMillis();
            System.out.println("执行时间为："+(stop-start));//504
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5 关闭资源
            JDBCUtils.closeResource(connection,preparedStatement);
        }
    }
    //方式四
    /**
     *
   	 * 		//设置不允许自动提交数据
     *  	connection.setAutoCommit(false);
	 *
     * 		//提交数据
     * 		connection.commit();
	 */
    @Test
    public void testInsert3() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            long start = System.currentTimeMillis();

            //1 获取连接
            connection = JDBCUtils.getConnection();
            //2 设置不允许自动提交
            connection.setAutoCommit(false);
            //2 预编译sql,返回PreparedStatment实例
            String sql = "insert into goods(name) values(?)";
            preparedStatement = connection.prepareStatement(sql);
            //3 填充占位符
            for (int i = 0; i < 2000; i++) {
                preparedStatement.setObject(1,"name_"+(i+1));
                //4 执行
                //4.1 攒sql
                preparedStatement.addBatch();
                if ((i+1)%500 == 0){
                    //4.2 执行
                    preparedStatement.executeBatch();
                    //4.3 清空batch
                    preparedStatement.clearBatch();
                }
            }
            //5 提交数据
            connection.commit();
            //6 恢复自动提交（主要是连接池有用）
            connection.setAutoCommit(true);

            long stop = System.currentTimeMillis();
            System.out.println("执行时间为："+(stop-start));//489
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //7 关闭资源
            JDBCUtils.closeResource(connection,preparedStatement);
        }
    }
    
}
